﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SD.IDAL;
using SD.Entities;
using System.Data.SqlClient;
using System.Data;

namespace SD.DAL
{
    public class DirectorateDAO : BaseDAO, IDirectorateDAO
    {

        #region IDirectorateDAO Members

        /// <summary>
        /// Get list of Directorate
        /// </summary>
        /// <param name="filterExpression">filterExpression parammeter</param>
        /// <param name="includeInactive">includeInactive parammeter</param>
        /// <returns>Return list of Directorate</returns>
        public List<Directorate> GetAll(string filterExpression, bool includeInactive, string orgId)
        {
            // Create a command object
            SqlCommand comm = CreateCommand();
            // Call command by store
            comm.CommandText = "DirectorateGetAll";
            // Add parammeter to store procedure
            comm.Parameters.AddWithValue("@filterExpression", filterExpression);
            comm.Parameters.AddWithValue("@includeInactive", includeInactive);
            comm.Parameters.AddWithValue("@OrgID", orgId);

            // Create a data table
            DataTable dt = ExecuteSelectCommand(comm);
            // Get list of directorate
            List<Directorate> list = new List<Directorate>();
            Directorate d = null;

            // Browse the list and add to table
            foreach (DataRow row in dt.Rows)
            {
                d = new Directorate();
                d.DirectorateId = row["DirectorateID"].ToString();
                d.Name = row["Name"].ToString();
                d.Address = row["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(row["AddressID"].ToString());
                d.Contact = row["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(row["ContactID"].ToString());                
                d.IsActive = bool.Parse(row["IsActive"].ToString());
                list.Add(d);
            }

            // Return the list
            return list;
        }
        /// <summary>
        /// Get list of Directorate
        /// </summary>
        /// <param name="filterExpression">filterExpression parammeter</param>
        /// <param name="includeInactive">includeInactive parammeter</param>
        /// <returns>Return list of Directorate</returns>
        public List<Directorate> GetAllFilter(bool includeInactive, string orgId)
        {
            // Create a command object
            SqlCommand comm = CreateCommand();
            // Call command by store
            comm.CommandText = "DirectorateGetAllFilter";
            // Add parammeter to store procedure        
            comm.Parameters.AddWithValue("@includeInactive", includeInactive);
            comm.Parameters.AddWithValue("@OrgID", orgId);
            // Create a data table
            DataTable dt = ExecuteSelectCommand(comm);
            // Get list of directorate
            List<Directorate> list = new List<Directorate>();
            Directorate d = null;

            // Browse the list and add to table
            foreach (DataRow row in dt.Rows)
            {
                d = new Directorate();
                d.DirectorateId = row["DirectorateID"].ToString();
                d.Name = row["Name"].ToString();
                d.Address = row["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(row["AddressID"].ToString());
                d.Contact = row["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(row["ContactID"].ToString());
                d.IsActive = bool.Parse(row["IsActive"].ToString());
                list.Add(d);
            }

            // Return the list
            return list;
        }

        /// <summary>
        /// Get list of Directorate by ID
        /// </summary>
        /// <param name="orgId">orgId parammeter</param>
        /// <returns>Return list of Diretorate by ID</returns>
        public List<Directorate> GetByOrgId(string orgId)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Get Directorate by ID
        /// </summary>
        /// <param name="directorateId">directorateId parammeter</param>
        /// <returns>Return Directorate</returns>
        public Directorate GetById(string directorateId)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "DirectorateGetById";

            // Add parameter to stored procedure
            comm.Parameters.AddWithValue("@DirectorateID", directorateId);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            Directorate dir = null;

            // if table has row
            if (dt.Rows.Count > 0)
            {

                dir = new Directorate();
                dir.DirectorateId=dt.Rows[0]["DirectorateID"].ToString();
                dir.Name = dt.Rows[0]["Name"].ToString();
                dir.ShortDescription = dt.Rows[0]["ShortDescription"].ToString();
                dir.Organization = dt.Rows[0]["OrgID"] == DBNull.Value ? null : new OrganizationDAO().GetById(dt.Rows[0]["OrgID"].ToString());
                dir.Address = dt.Rows[0]["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(dt.Rows[0]["AddressID"].ToString());
                dir.Contact = dt.Rows[0]["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(dt.Rows[0]["ContactID"].ToString());
                dir.FullDescription = dt.Rows[0]["FullDescription"].ToString();
                dir.Phone = dt.Rows[0]["Phone"].ToString();
                dir.Fax = dt.Rows[0]["Fax"].ToString();
                dir.Web = dt.Rows[0]["Web"].ToString();
                dir.Charity = dt.Rows[0]["Charity"].ToString();
                dir.Company = dt.Rows[0]["Company"].ToString();
                dir.IsActive = bool.Parse(dt.Rows[0]["IsActive"].ToString());
                dir.BusinessType = dt.Rows[0]["BusinessType"] == DBNull.Value ? null : new ReferenceDataDAO().GetById(dt.Rows[0]["BusinessType"].ToString());
                dir.Email = dt.Rows[0]["Email"].ToString();
                

            }

            // return true if success fully, otherwise false
            return dir;        
        }

        /// <summary>
        /// Insert directorate into system
        /// </summary>
        /// <param name="directorate">directorate parammeter</param>
        /// <returns>Return result of insert. If insert success return true, else return false</returns>
        public string Insert(Directorate directorate)
        {
            string result;

            // Create new command object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "DirectorateInsert";

            // Add parameters to commnand
            comm.Parameters.AddWithValue("@Name", directorate.Name);
            comm.Parameters.AddWithValue("@ShortDescription", directorate.ShortDescription);
            comm.Parameters.AddWithValue("@ContactID", directorate.Contact == null ? null : directorate.Contact.ContactId);
            comm.Parameters.AddWithValue("@AddressID", directorate.Address.AddressId);
            comm.Parameters.AddWithValue("@FullDescription", directorate.FullDescription);
            comm.Parameters.AddWithValue("@Phone", directorate.Phone);
            comm.Parameters.AddWithValue("@Fax", directorate.Fax);
            comm.Parameters.AddWithValue("@Web", directorate.Web);
            comm.Parameters.AddWithValue("@Charity", directorate.Charity);
            comm.Parameters.AddWithValue("@Company", directorate.Company);
            comm.Parameters.AddWithValue("@IsActive", directorate.IsActive);
            comm.Parameters.AddWithValue("@BusinessType", directorate.BusinessType.RefId);
            comm.Parameters.AddWithValue("@OrgID", directorate.Organization.OrgId);
            comm.Parameters.AddWithValue("@Email", directorate.Email);

            try
            {
                comm.Connection.Open();
                // Execute the stored procedure
                result = comm.ExecuteScalar().ToString();
                comm.Connection.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Result will be 1 in case of success
            return result;
        }
        public List<Directorate> GetDirByName(string dirName)
        {
            // Create new SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "DirectorateGetByName";

            // Add parameters to stored procedure
            comm.Parameters.AddWithValue("@DirName", dirName);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            List<Directorate> list = new List<Directorate>();

            // add each org to list
            foreach (DataRow row in dt.Rows)
            {
                Directorate dir = new Directorate();
                dir.DirectorateId = row["DirectorateID"].ToString();
                dir.Name = row["Name"].ToString();
                dir.Address = row["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(row["AddressID"].ToString());
                dir.Contact = row["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(row["ContactID"].ToString());
                dir.IsActive = bool.Parse(row["IsActive"].ToString());
                list.Add(dir);
            }

            // return list organization
            return list;
        }
        /// <summary>
        /// Update directorate into system
        /// </summary>
        /// <param name="directorate">directorate parammeter</param>
        /// <returns>Return the result of update. If update success return true, else return false</returns>
        public bool Update(Directorate directorate)
        {
            int result;

            // Create new SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure to command
            comm.CommandText = "DirectorateUpdate";

            // Add parameters to command
            comm.Parameters.AddWithValue("@DirectorateID", directorate.DirectorateId);
            comm.Parameters.AddWithValue("@Name", directorate.Name);
            comm.Parameters.AddWithValue("@ShortDescription", directorate.ShortDescription);
            comm.Parameters.AddWithValue("@ContactID", directorate.Contact == null ? null : directorate.Contact.ContactId);
            comm.Parameters.AddWithValue("@AddressID", directorate.Address == null ? null : directorate.Address.AddressId);
            comm.Parameters.AddWithValue("@FullDescription", directorate.FullDescription);
            comm.Parameters.AddWithValue("@Phone", directorate.Phone);
            comm.Parameters.AddWithValue("@Fax", directorate.Fax);
            comm.Parameters.AddWithValue("@Web", directorate.Web);
            comm.Parameters.AddWithValue("@Charity", directorate.Charity);
            comm.Parameters.AddWithValue("@Company", directorate.Company);
            comm.Parameters.AddWithValue("@IsActive", directorate.IsActive);
            comm.Parameters.AddWithValue("@BusinessType", directorate.BusinessType == null ? null : directorate.BusinessType.RefId);
            comm.Parameters.AddWithValue("@Email", directorate.Email);

            try
            {
                // Execute the stored procedure
                result = ExecuteNonQuery(comm);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Result will be 1 in case of success
            return (result != -1);
        }

        /// <summary>
        /// Delete directorate from system
        /// </summary>
        /// <param name="directorateId">directorateId parammeter</param>
        /// <returns>Return the result of delete. If delete success return true, else return false</returns>
        public bool Delete(string directorateId)
        {
            throw new NotImplementedException();
        }
        /// <summary>
        /// Set Active a Directorate
        /// </summary>
        /// <param name="dirId"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public bool SetActive(string dirId, bool status)
        {
            int result;

            // Create new SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure to command
            comm.CommandType = CommandType.Text;
            comm.CommandText = "UPDATE Directorate SET IsActive = @IsActive WHERE DirectorateID = @DirID";

            comm.Parameters.AddWithValue("@DirID", dirId);
            comm.Parameters.AddWithValue("@IsActive", status);

            try
            {
                // Execute the stored procedure
                result = ExecuteNonQuery(comm);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Result will be 1 in case of success
            return (result != -1);
        }
        #endregion
    }
}
